from flask import Flask, jsonify, request, render_template  
import sqlite3  
import torch  
import torch.nn.functional as F 
import pandas as pd 

# 创建数据库连接  
conn = sqlite3.connect('health_records.db')  
c = conn.cursor()  

# 检查 records 表是否存在  
c.execute("SELECT count(name) FROM sqlite_master WHERE type='table' AND name='records'")  
if c.fetchone()[0] == 0:  
    # 如果 records 表不存在,则创建表  
    c.execute('''CREATE TABLE records  
                 (id INTEGER PRIMARY KEY AUTOINCREMENT, 
                  patient_id INTEGER NOT NULL,    
                  timestamp TEXT,   
                  pregnancies INTEGER,   
                  glucose REAL,   
                  bloodpressure REAL,   
                  skinthickness REAL,   
                  insulin REAL,   
                  bmi REAL,   
                  dpf REAL,   
                  age INTEGER,   
                  prediction INTEGER)''')  
    print("Created 'records' table.")  

# 关闭数据库连接  
conn.close()  

class Net(torch.nn.Module):  
    def __init__(self):  
        super(Net, self).__init__()  
        self.linear1 = torch.nn.Linear(8, 72)  
        self.linear2 = torch.nn.Linear(72, 64)  
        self.linear3 = torch.nn.Linear(64, 1)  

    def forward(self, x):  
        x = F.relu(self.linear1(x))  
        x = F.relu(self.linear2(x))  
        x = F.relu(self.linear3(x))  
        return x  

app = Flask(__name__)  
model = Net()  
model.load_state_dict(torch.load('./Ddiabetes_model.pth'))  
model.eval()  

@app.route('/')  
def index():  
    return render_template('index.html')  

@app.route('/predict', methods=['POST'])  
def predict():  
    pregnancies = float(request.form['pregnancies'])  
    glucose = float(request.form['glucose'])  
    bloodpressure = float(request.form['bloodpressure'])  
    skinthickness = float(request.form['skinthickness'])  
    insulin = float(request.form['insulin'])  
    bmi = float(request.form['bmi'])  
    dpf = float(request.form['dpf'])  
    age = float(request.form['age']) 
    
    patient_id = float(request.form['patient_id'])

    try:  
        features = [pregnancies, glucose, bloodpressure, skinthickness, insulin, bmi, dpf, age]  
        inputs = torch.tensor(features).to(torch.float32).reshape(1, -1)  

        with torch.no_grad():  
            prediction = model(inputs)  

        probability = torch.sigmoid(prediction).item()  
        binary_prediction = 1 if probability == 1 else 0  

        # Save the record to the database  
        conn = sqlite3.connect('health_records.db')  
        c = conn.cursor()  
        c.execute("INSERT INTO records (patient_id, timestamp, pregnancies, glucose, bloodpressure, skinthickness, insulin, bmi, dpf, age, prediction) VALUES (?, DATETIME('now', 'localtime'), ?, ?, ?, ?, ?, ?, ?, ?, ?)",   
                  (patient_id, pregnancies, glucose, bloodpressure, skinthickness, insulin, bmi, dpf, age, binary_prediction))   
        conn.commit()  
        conn.close()  

        return render_template('result.html', prediction=binary_prediction)  

    except Exception as e:  
        return render_template('result.html', prediction=binary_prediction, glucose=glucose, bloodpressure=bloodpressure, skinthickness=skinthickness, insulin=insulin, bmi=bmi, dpf=dpf, age=age)  

@app.route('/history')  
def history():  
    return render_template('history.html')    

@app.route('/history_data')  
def history_data():  
    date = request.args.get('date', None)  # 获取日期参数  
    page = request.args.get('page', 1, type=int)  # 获取页码参数，默认是第1页  
    records, total_records = get_history_records(page=page, date=date)  # 获取记录和总条数  
    return jsonify({'records': records, 'total_records': total_records})   

def get_history_records(page=1, limit=10, date=None):  
    conn = sqlite3.connect('health_records.db')  
    c = conn.cursor()  

    query = "SELECT * FROM records"  
    params = []  

    if date:  
        query += " WHERE DATE(timestamp) = ?"  
        params.append(date)  

    # 获取总记录数  
    total_records = c.execute(query, tuple(params)).fetchall()  

    # 添加排序和分页  
    query += " ORDER BY timestamp DESC LIMIT ? OFFSET ?"  
    params.append(limit)  
    params.append((page - 1) * limit)  # 计算偏移量  

    records = c.execute(query, tuple(params)).fetchall()  
    conn.close()  
    return records, len(total_records)  # 返回记录和总条数 

@app.route('/patient_data')  
def patient_data():  
    patient_id = request.args.get('patient_id')  # 获取患者ID参数  
    if patient_id:  
        records = get_patient_records(patient_id)  # 根据患者ID获取记录  
        return jsonify({'records': records})  
    return jsonify({'records': []})  # 如果没有提供患者ID，则返回空记录  

def get_patient_records(patient_id):   
    conn = sqlite3.connect('health_records.db')  
    c = conn.cursor()  

    query = "SELECT * FROM records WHERE patient_id = ? ORDER BY timestamp DESC"  
    records = c.execute(query, (patient_id,)).fetchall()  
    conn.close()  
    return records  


@app.route('/diabetes_stats')  
def diabetes_stats():    
    total_diabetic, total_normal = get_diabetes_statistics()  # 获取统计数据  
    return jsonify({'diabetic': total_diabetic, 'normal': total_normal})  

def get_diabetes_statistics():  
    conn = sqlite3.connect('health_records.db')  
    c = conn.cursor()  
  
    total_diabetic = c.execute("SELECT COUNT(*) FROM records WHERE prediction = 1").fetchone()[0]  
    total_normal = c.execute("SELECT COUNT(*) FROM records WHERE prediction = 0").fetchone()[0]  
    conn.close()  

    return total_diabetic, total_normal

@app.route('/diabetes_means')  
def diabetes_means():  
    """  
    获取正常患者和糖尿病患者各项指标的均值。  
    """  
    means = get_diabetes_means()  # 获取均值数据  
    return jsonify(means)  

def get_diabetes_means():   
    conn = sqlite3.connect('health_records.db')  
    c = conn.cursor()  

    # 计算正常患者的均值  
    normal_means = c.execute("""  
        SELECT AVG(pregnancies), AVG(glucose), AVG(bloodPressure), AVG(skinThickness),   
               AVG(insulin), AVG(bmi), AVG(age)   
        FROM records WHERE prediction = 0  
    """).fetchone()  

    # 计算糖尿病患者的均值  
    diabetic_means = c.execute("""  
        SELECT AVG(pregnancies), AVG(glucose), AVG(bloodPressure), AVG(skinThickness),   
               AVG(insulin), AVG(bmi), AVG(age)   
        FROM records WHERE prediction = 1  
    """).fetchone()  

    conn.close()  

    return {  
        'normal': normal_means,  
        'diabetic': diabetic_means  
    }

def get_xxdata_from_db():    
    conn = sqlite3.connect('health_records.db')  
    query = "SELECT pregnancies, glucose, bloodpressure, skinthickness, insulin, bmi, dpf, age, prediction FROM records"  
    df = pd.read_sql_query(query, conn)  
    conn.close()  
    return df  


@app.route('/api/xxdata')  
def data():  
    df = get_xxdata_from_db()  
    data = df.to_dict(orient='records')  
    return jsonify(data) 


if __name__ == '__main__':  
    app.run(debug=True)